﻿using BySoft.Server.Common;
using BySoft.Server.Common.Entity;
using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess
{
    public partial class BsProductinstockDA
    {
        public static DataTable SelectList(string strBillID, string strProductID, string strWorkOrderNo, string strFrom, string strEnd, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd = @"select  a.*,
 
   p.ProductName,p.Specifications,p.Unit,
   pt.ProductTypeName,p.CustomerPartNo,p.OutSide,p.InSide,
  
   bsd.ScheduleDetailID,bsd.PlanID,
   bsd.ScheduleNum,bsd.ExpectInStockDate, bsd.Remark,
  -1 as IsNew,-1 as IsDel
from Bs_ProductInStock a
LEFT JOIN  BS_Schedule AS bs ON a.ScheduleID=bs.ScheduleID
   left join BS_ScheduleDetail AS bsd ON bsd.ScheduleID = bs.ScheduleID

LEFT JOIN md_Product p ON a.ProductID=p.ProductID
LEFT JOIN md_ProductType pt ON p.ProductTypeID=pt.ProductTypeID

  WHERE a.[Status]='1' ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(strBillID))
                {
                    strcmd += " and a.BillID like @strBillID";
                    dpc.AddWithValue("@strBillID", "%" + strBillID + "%");
                }

                if (!string.IsNullOrEmpty(strProductID))
                {
                    strcmd += " and p.ProductID like @strProductID";
                    dpc.AddWithValue("@strProductID", "%" + strProductID + "%");
                }
                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and a.WorkOrderNo Like @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", "%" + strWorkOrderNo + "%");
                }

                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and a.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strEnd))
                {
                    strcmd += " and a.BillDate <= @strEnd";
                    dpc.AddWithValue("@strEnd", strEnd);
                }
                strcmd += " order by a.BillDate desc";



                DataTable dt = DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);


                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        /// <summary>
        /// 排程单中没有结案的
        /// 
        /// </summary>

        /// <returns></returns>
        public static DataTable SelectDialogList(string strCurrentID, string strProductID, string strProductName, string strOrderID,
            string strWorkOrderNo,string strScheduleID, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd = @"select  bod.OrderID, bod.ProductID, bod.Nums AS OrderNum, 
   p.ProductName,p.Specifications,p.Unit,
   pt.ProductTypeName,p.CustomerPartNo,p.OutSide,p.InSide,
   bsd.ScheduleID,bsd.ScheduleDetailID,
   bsd.WorkOrderNo,bsd.PlanID,
   bsd.ScheduleNum,isnull(ret.ReturnNums,0) AS ReturnNum ,
   bsd.ScheduleNum-isnull(ret.ReturnNums,0) as LastNum,
   bsd.ExpectInStockDate, bsd.Remark,
  -1 as IsNew,-1 as IsDel
FROM   

BS_Schedule AS bs 

   left join BS_ScheduleDetail AS bsd ON bsd.ScheduleID = bs.ScheduleID
LEFT JOIN BS_OrderDetail AS bod ON bod.WorkOrderNo=bsd.WorkOrderNo
LEFT JOIN md_Product p ON bod.ProductID=p.ProductID
LEFT JOIN md_ProductType pt ON p.ProductTypeID=pt.ProductTypeID
 LEFT JOIN  (
        
        SELECT ProductID,WorkOrderNo,ScheduleID,sum(Nums) as ReturnNums 
            from Bs_ProductInStock aR 
           where aR.BillID<>@CurrentBillID
             group by  ProductID,WorkOrderNo,ScheduleID
             )  ret on ret.ProductID=bod.ProductID 
             AND  bsd.WorkOrderNo=ret.WorkOrderNo
             AND  bsd.ScheduleID =ret.ScheduleID
             
WHERE isnull(ret.ReturnNums,0)<bsd.ScheduleNum --未达数量

and isnull(bsd.isFinish,'') <>'1'  --未结案  

   ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();


                dpc.AddWithValue("@CurrentBillID", strCurrentID);

                if (!string.IsNullOrEmpty(strOrderID))
                {
                    strcmd += " and bod.OrderID like  @strOrderID";
                    dpc.AddWithValue("@strOrderID", "%" + strOrderID + "%");
                }

                if (!string.IsNullOrEmpty(strProductID))
                {
                    strcmd += " and p.ProductID like @strProductID";
                    dpc.AddWithValue("@strProductID", "%" + strProductID + "%");
                }
                if (!string.IsNullOrEmpty(strProductName))
                {
                    strcmd += " and p.ProductName like @strProductName";
                    dpc.AddWithValue("@strProductName", "%" + strProductName + "%");
                }
                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and bsd.WorkOrderNo = @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", strWorkOrderNo);
                }

                if (!string.IsNullOrEmpty(strScheduleID))
                {
                    strcmd += " and bsd.ScheduleID = @strScheduleID";
                    dpc.AddWithValue("@strScheduleID", strScheduleID);
                }
                strcmd += " order by bod.ProductID desc";



                DataTable dt = DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);


                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }




        #region View Data
        public static DataTable SelectHeadData(string strBillID)
        {
            try
            {
                string strcmd = @"       SELECT a.*,                           
    p.ProductName,p.Specifications,p.Unit,
   pt.ProductTypeName,p.CustomerPartNo,p.OutSide,p.InSide,
-1 as IsNew,-1 as IsDel
from Bs_ProductInStock a   

LEFT JOIN md_Product p ON a.ProductID=p.ProductID
LEFT JOIN md_ProductType pt ON p.ProductTypeID=pt.ProductTypeID  


where a.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@BillID", strBillID);
                DataTable dt = DataAccessCommon.Select(strcmd, dpc).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }


        #endregion



        #region AUD
        public static bool SaveOrUpdate(BsProductinstockEntity entity, bool isNew)
        {
            bool istag = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                broker.BeginTransaction();
                try
                {
                    if (isNew)
                    {
                        DataAccessCommon.Insert(entity, broker);
                    }
                    else
                    {
                        DataAccessCommon.UpdateOnly(entity, broker);


                    }

                    broker.Commit();
                }
                catch
                {
                    istag = false;
                    broker.Rollback();
                    throw;
                }
                return istag;
            }
        }

        public static bool Del(string strCode)
        {
            bool isResult = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    DataAccessCommon.Delete(strCode, "BillID", "BS_Productinstock");
                    broker.Commit();
                }
                catch (Exception ex)
                {
                    isResult = false;
                    broker.Rollback();
                    throw ex;
                }
            }
            return isResult;
        }
        #endregion
    }
}
